﻿using BwTMusicModels;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTMusicServices.Singer
{
    public class SingerServices
    {
        public List<North_Singer> GetTopSixSinger()
        {
            List<North_Singer> singers = new List<North_Singer>();
            string sql = "select top 6 * from North_Singer";
            using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
            {
                while (dr.Read())
                {
                    North_Singer list = new North_Singer();
                    list = new ModelsHelper().GetNorthSingerModels(dr);
                    list.MusicCount = SingerMusicCount(list.SingerId);
                    singers.Add(list);
                }
            }
            return singers;
        }

        public List<North_Singer> GetAllNorthSinger(int CurrentPage,string chars,string countrys ,out int total)
        {
            List<North_Singer> Lists = new List<North_Singer>();
            total = 0;
            try
            {
                if (CurrentPage > 0)
                {
                    StringBuilder sbSQL = new StringBuilder();
                    List<SqlParameter> listParam = new List<SqlParameter>();
                    sbSQL.Append("select top 24");
                    sbSQL.Append($" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by SingerId) as rownumber, * from North_Singer where 1=1   ");
                    if (!string.IsNullOrEmpty(chars))
                    {
                        sbSQL.Append($"and SingerFName='{chars.ToUpper()}' ");
                    }
                    if (!string.IsNullOrEmpty(countrys))
                    {
                        sbSQL.Append($"and SingerCountry='{countrys}' ");
                    }
                    sbSQL.Append(") WTX where rownumber > (");
                    sbSQL.Append((CurrentPage - 1) * 24);
                    sbSQL.Append(")");
                    using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                    {
                        while (dr.Read())
                        {
                            int iIndex = 0;
                            North_Singer list = new ModelsHelper().GetNorthSingerModels(dr);     
                            list.MusicCount = SingerMusicCount(list.SingerId);
                            Lists.Add(list);
                            if (iIndex == 0)
                            {
                                total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                            }
                            iIndex++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return Lists;
        }

        public int SingerMusicCount(int id)
        {
            int Count = 0;
            try
            {
                string sql = $"select count(*) from SingerMusic where SingerId='{id}'";
                Count = (int)DatabaseFactory.GetDatabase().ExecuteDataScalarSQL(sql);
            }
            catch (Exception)
            {
            }
            return Count;
        }  
        
        public North_Singer GetSingerInfoById(int id)
        {
            North_Singer singer = null;

            try
            {
                string sql = $"select A.SingerId,A.SingerName,A.SingerBir,A.SingerDesc,A.SingerPictrue from North_Singer A  where A.SingerId='{id}'";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        singer = new North_Singer();
                        singer = new ModelsHelper().GetNorthSingerModels(dr);
                    }
                }
            }
            catch (Exception ex)
            {

                throw;
            }
            return singer;
        }
    }
}
